﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using InfoClasses;

namespace DbControl
{
/// <summary>
/// Class that contains methods for interacting with databases
/// </summary>

public class DBControl
{
    private const string _serverName = "VADYMP\\NICE";
    private const string _dbName = "ToursDB";
    private const string _usersID = "NICE_SYSTEMS/vadymp";
    public string Output = "";

    public DataSet _dataSet = new DataSet();
    public const string DbConnection = "Server=" + _serverName + ";Database=" +
        _dbName + ";User Id=" + _usersID + ";Trusted_Connection = true";
    public SqlConnection DbConnect = new SqlConnection(DbConnection);

    public DBControl()
    {
        try
        {
            DbConnect.Open();
        }

        catch (Exception err)
        {
            Console.WriteLine("An error occurred while connecting, " +
                "read error massage and try to fix problem");
            Console.WriteLine(err.Message);
        }
    }

    /// <summary>
    /// Add new row to table that seted in "InfoObject"
    /// </summary>
    /// <param name="InfoObject"></param>

    public void AddNewRow(Info InfoObject)
    {
        string _curTable = InfoObject.WorkTable;
        string _query = "SELECT * FROM " + _curTable;
        SqlDataAdapter _dataAdapter = new SqlDataAdapter();
        _dataAdapter.SelectCommand = new SqlCommand(_query, DbConnect);

        if (_curTable == "Tourists")
        {
            _dataAdapter.InsertCommand = new SqlCommand("INSERT INTO " +
                _curTable + "(FirstName, MiddleName, LastName, PasportID)" +
                "VALUES" + "(@FirstName, @MiddleName, @LastName, @PasportID)",
                DbConnect);
            _dataAdapter.Fill(_dataSet, _curTable);

            SqlParameter _workParameter = null;
            //setting required parameters
            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@TouristID", SqlDbType.Int));
            _workParameter.SourceColumn = "TouristID";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@FirstName", SqlDbType.NChar, 15));
            _workParameter.SourceColumn = "FirstName";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@MiddleName", SqlDbType.NChar, 15));
            _workParameter.SourceColumn = "MiddleName";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@LastName", SqlDbType.NChar, 15));
            _workParameter.SourceColumn = "LastName";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@PasportID", SqlDbType.NChar, 15));
            _workParameter.SourceColumn = "PasportID";
            _workParameter.SourceVersion = DataRowVersion.Current;
        }

        if (_curTable == "Tours")
        {
            _dataAdapter.InsertCommand = new SqlCommand("INSERT INTO " +
                _curTable + "(TouristID, Country, StartOFTour, EndOfTour)" +
                "VALUES(@TouristID, @Country, @StartOFTour, @EndOfTour)",
                DbConnect);
            _dataAdapter.Fill(_dataSet, _curTable);

            SqlParameter _workParameter = null;
            //setting required parameters
            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@ToursID", SqlDbType.Int));
            _workParameter.SourceColumn = "ToursID";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@TouristID", SqlDbType.Int));
            _workParameter.SourceColumn = "TouristID";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@Country", SqlDbType.NChar, 15));
            _workParameter.SourceColumn = "Country";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@StartOfTour", SqlDbType.Date));
            _workParameter.SourceColumn = "StartOfTour";
            _workParameter.SourceVersion = DataRowVersion.Current;

            _workParameter = _dataAdapter.InsertCommand.Parameters.Add(new
                SqlParameter("@EndOFTour", SqlDbType.Date));
            _workParameter.SourceColumn = "EndOfTour";
            _workParameter.SourceVersion = DataRowVersion.Current;
        }

        DataRow newRow = _dataSet.Tables[_curTable].NewRow();

        if (InfoObject.WorkTable == "Tourists")
        {
            newRow[1] = InfoObject.FirstName;
            newRow[2] = InfoObject.MiddleName;
            newRow[3] = InfoObject.LastName;
            newRow[4] = InfoObject.PasportID;
        }
        if (InfoObject.WorkTable == "Tours")
        {
            newRow[1] = InfoObject.TouristID;
            newRow[2] = InfoObject.Country;
            newRow[3] = InfoObject.StartOfTour;
            newRow[4] = InfoObject.EndOfTour;
        }
        _dataSet.Tables[_curTable].Rows.Add(newRow);

        Console.WriteLine(_dataSet.GetXml());
        try
        {
            _dataAdapter.Update(_dataSet, _curTable);
            _dataSet.Dispose();
            _dataSet = new DataSet();
            _dataAdapter.Fill(_dataSet, _curTable);
        }

        catch (Exception err)
        {
            Console.WriteLine("Something wrong");
            Console.WriteLine(err.Message);
        }
    }

    /// <summary>
    /// Show current table in console
    /// </summary>
    /// <param name="InfoObject"></param>

    public void ShowTable(Info InfoObject)
    {
        string _query = "SELECT * FROM " + InfoObject.WorkTable;
        int n = 0;

        DataSet _dataSet2 = new DataSet();
        SqlDataAdapter _dataAdapter = new SqlDataAdapter();
        _dataAdapter.SelectCommand = new SqlCommand(_query, DbConnect);
        _dataAdapter.Fill(_dataSet2, _query);

        DataTable _dt = new DataTable();
        _dt = _dataSet2.Tables[0];

        foreach (DataColumn column in _dt.Columns)
        {
            InfoObject._collumnNames.Add(column.ColumnName);
        }

        if (InfoObject.WorkTable == "Tours")
        {
            
            Console.WriteLine(String.Format("{0,-10}{1,-10}    {2,-10}      {3,-10} {4,-10}\n",
                InfoObject._collumnNames[0], InfoObject._collumnNames[1],
                InfoObject._collumnNames[2], InfoObject._collumnNames[3],
                InfoObject._collumnNames[4]));

            SqlCommand command = new SqlCommand(_query, DbConnect);
            SqlDataReader _dataReader = command.ExecuteReader();

            while (_dataReader.Read())
            {
                n = 0;
                for (int i = 0; i < _dataReader.FieldCount; i++)
                {
                    if (n == 3)
                    {
                        Console.Write(String.Format("{0,-12 : yyyy-MM-dd}", _dataReader[i]));
                    }
                    else
                    {
                        Console.Write(String.Format("{0,-12}", _dataReader[i]));
                        n++;
                    }

                }
                Console.WriteLine();
            }

            _dataReader.Close();
        }
        else 
        {
            Console.WriteLine(String.Format("{0,-10}  {1,-10}     {2,-10}     {3,-10}     {4,-10}\n",
                InfoObject._collumnNames[0], InfoObject._collumnNames[1],
                InfoObject._collumnNames[2], InfoObject._collumnNames[3],
                InfoObject._collumnNames[4]));

            SqlCommand command = new SqlCommand(_query, DbConnect);
            SqlDataReader _dataReader = command.ExecuteReader();

            while (_dataReader.Read())
            {
                for (int i = 0; i < _dataReader.FieldCount; i++)
                {
                    Console.Write(String.Format("{0,-12}", _dataReader[i]));
                }
                Console.WriteLine();
            }
            _dataReader.Close();
        }
    }

    /// <summary>
    /// Print table in file
    /// </summary>
    /// <param name="InfoObject"></param>

    public void PrintTable(Info InfoObject)
    {
        string _query = "SELECT * FROM " + InfoObject.WorkTable;

        DataSet _dataSet2 = new DataSet();
        SqlDataAdapter _dataAdapter = new SqlDataAdapter();
        _dataAdapter.SelectCommand = new SqlCommand(_query, DbConnect);
        _dataAdapter.Fill(_dataSet2, _query);

        StreamWriter _tableWriter = File.CreateText("C:/Table.csv");
        System.Console.WriteLine("****** Table printed in file. You will find it in: C:/Table.csv**********");

        DataTable _dt = new DataTable();
        _dt = _dataSet2.Tables[0];

        foreach (DataColumn column in _dt.Columns)
        {
            InfoObject._collumnNames.Add(column.ColumnName);
        }

        _tableWriter.WriteLine(String.Format("{0},{1},{2},{3},{4},",
            InfoObject._collumnNames[0], InfoObject._collumnNames[1],
            InfoObject._collumnNames[2], InfoObject._collumnNames[3],
            InfoObject._collumnNames[4]));


        SqlCommand command = new SqlCommand(_query, DbConnect);
        SqlDataReader _dataReader = command.ExecuteReader();

        while (_dataReader.Read())
        {
            for (int i = 0; i < _dataReader.FieldCount; i++)
            {
                _tableWriter.Write(String.Format("{0},", _dataReader[i]));
            }
            _tableWriter.WriteLine();
        }

        _tableWriter.Close();
        _dataReader.Close();
    }

    /// <summary>
    /// Delete chosen row from table
    /// </summary>
    /// <param name="TargetTable">Table to delete row in</param>
    /// <param name="TargetID">ID of row to delete</param>

    public void DeleteRow(string TargetTable, int TargetID)
    {
        string _delquery = "";
        string _delchoise = "";
        Console.WriteLine("CAUTION: DELETING INFORMATION FROM TOURISTS MAY CAUSE DELETING RELATED INFORMATION FROM TOURS!");

        if (TargetTable == "Tourists")
        {
            _delchoise = "TouristID";
        }

        if (TargetTable == "Tours")
        {
            _delchoise = "ToursID";
        }

        SqlDataAdapter _deladapter = new SqlDataAdapter();
        _delquery = "SELECT * FROM " + TargetTable;
        _deladapter.SelectCommand = new SqlCommand(_delquery, DbConnect);
        _deladapter.Fill(_dataSet);
        SqlCommand _delcommand = new SqlCommand(("DELETE FROM " + TargetTable + " WHERE " + _delchoise + " = " + TargetID + ""), DbConnect);
        _delcommand.ExecuteNonQuery();
        try
        {
            _deladapter.Update(_dataSet);
        }

        catch (Exception err)
        {
            Console.WriteLine("An error occurred while processing, read error massage and try to fix problem");
            Console.WriteLine(err.Message);
        }

    }

    /// <summary>
    /// Method for edit & update info in rows of data base 
    /// </summary>
    /// <param name="TargetTable">Target bable to edit row</param>
    /// <param name="TargetID">Target table to edit</param>
    /// 

    public void Update(string TargetTable, int TargetID)
    {
        SqlDataAdapter _updAdapter = new SqlDataAdapter();
        DataSet _dataSet3 = new DataSet();
        string _updquery = "";

        if (TargetTable == "Tourists")
        {
            #region
            bool _mark = true;
            ConsoleKeyInfo _key = new ConsoleKeyInfo();
            _updquery = "SELECT * FROM " + TargetTable + " WHERE TouristID='" + TargetID + "'";


            while (_mark == true)
            {
                // Create DataRow object, fill it by consisting info from base,
                // Ask user to what info to change, and refill chosen parts of it. Then - update it to base.
                Info InfoObject = new TouristsInfo();
                _updAdapter.SelectCommand = new SqlCommand(_updquery, DbConnect);
                _updAdapter.Fill(_dataSet3, InfoObject.WorkTable);

                //DataRow _dr = _dataSet3.Tables[InfoObject.WorkTable].Rows.Find(0);

                DataRow _dr = _dataSet3.Tables[InfoObject.WorkTable].Rows[0];

                Console.WriteLine("\nChose Info to update:" +
                    "\nUpdate First name of tourist:                    (Press F)" +
                    "\nUpdate Middle name of tourist:                   (Press M)" +
                    "\nUpdate Last name of tourist:                     (Press L)" +
                    "\nUpdate Passport ID of tourist:                   (Press P)" +
                    "\nShow new info:                                   (Press S)" +
                    "\nTo apply changes and update the record:          (Press Enter)" +
                    "\nTo discart changes and/or go to previous menu:   (Press B)");
                _key = Console.ReadKey(true);
                switch (_key.Key)
                {
                    case ConsoleKey.F:
                        {
                            #region
                            // Place code here
                            Console.WriteLine("\nType new first name:\n");
                            _dr["FirstName"] = Console.ReadLine();
                            break;
                            #endregion
                        }

                    case ConsoleKey.M:
                        {
                            #region
                            // Place code here
                            Console.WriteLine("\nType new middle name:\n");
                            _dr["MiddleName"] = Console.ReadLine();
                            break;
                            #endregion
                        }

                    case ConsoleKey.L:
                        {
                            #region
                            Console.WriteLine("\nType new last name:\n");
                            _dr["LastName"] = Console.ReadLine();
                            break;
                            #endregion
                        }

                    case ConsoleKey.P:
                        {
                            #region
                            Console.WriteLine("\nType new passport ID:\n");
                            _dr["PasportID"] = Console.ReadLine();
                            break;
                            #endregion
                        }
                    case ConsoleKey.S:
                        {
                            #region
                            Console.WriteLine("{0}  {1}  {2}  {3}  {4}", TargetID, _dr.ItemArray[1],
                            _dr.ItemArray[2], _dr.ItemArray[3], _dr.ItemArray[4]);
                            break;
                            #endregion
                        }
                    case ConsoleKey.Enter:
                        {
                            #region
                            Console.WriteLine("\n\nCAUTION: AFTER THIS OPERATION CHANGES WOULD NOT BE ALLOWED TO DECLINE!" +
                                "\nType: Y to ACCEPT/ N to DECLINE changes ");
                            _key = Console.ReadKey(true);
                            switch (_key.Key)
                            {
                                case ConsoleKey.Y:
                                    {
                                        _updAdapter.UpdateCommand = new SqlCommand("UPDATE " + TargetTable + " SET FirstName=@FirstName, "
                                            + "MiddleName=@MiddleName, LastName=@LastName, PasportID=@PasportID WHERE TouristID='" + TargetID + "'"
                                            , DbConnect);

                                        SqlParameter UpdParam = null;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@TouristID", SqlDbType.Int));
                                        UpdParam.SourceColumn = "TouristID";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@FirstName", SqlDbType.NChar, 20));
                                        UpdParam.SourceColumn = "FirstName";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@MiddleName", SqlDbType.NChar, 20));
                                        UpdParam.SourceColumn = "MiddleName";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@LastName", SqlDbType.NChar, 20));
                                        UpdParam.SourceColumn = "LastName";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@PasportID", SqlDbType.NChar, 10));
                                        UpdParam.SourceColumn = "PasportID";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        try
                                        {
                                            _updAdapter.Update(_dataSet3, "Tourists");
                                            _updAdapter.Dispose();
                                            Console.WriteLine("\nAll changes are saved.");
                                        }

                                        catch (Exception e)
                                        {
                                            Console.WriteLine(e.ToString());
                                        }

                                        break;
                                    }
                                case ConsoleKey.N:
                                    {
                                        Console.WriteLine("\nAll changes are cleared, start again");
                                        _mark = false;
                                        break;
                                    }
                                default:
                                    {
                                        break;
                                    }
                            }

                            break;
                            #endregion
                        }

                    case ConsoleKey.B:
                        {
                            #region
                            // Place code here
                            _mark = false;
                            break;
                            #endregion
                        }
                }

            }
            Console.WriteLine("\n");
            #endregion
        }

        if (TargetTable == "Tours")
        {
            #region
            bool _mark = true;
            ConsoleKeyInfo _key = new ConsoleKeyInfo();
            _updquery = "SELECT * FROM " + TargetTable + " WHERE TouristID='" + TargetID + "'";

            while (_mark == true)
            {
                // Create DataRow object, fill it by consisting info from base,
                // ask user to what info to change, and refill chosen parts of it. then - update it to base.
                Info InfoObject = new ToursInfo();
                _updAdapter.SelectCommand = new SqlCommand(_updquery, DbConnect);
                _updAdapter.Fill(_dataSet3, InfoObject.WorkTable);
                DataRow _dr = _dataSet3.Tables[InfoObject.WorkTable].Rows[0];
                _dr["ToursID"] = TargetID.ToString();

                Console.WriteLine("\nChose Info to update:" +
                    "\nUpdate ID of tourist:                            (Press T)" +
                    "\nUpdate Country:                                  (Press C)" +
                    "\nUpdate Start tour date:                          (Press S)" +
                    "\nUpdate End tour date:                            (Press E)" +
                    "\nShow new info:                                   (Press N)" +
                    "\nTo apply changes and update the record:          (Press Enter)" +
                    "\nTo discart changes and/or go to previous menu:   (Press B)");

                _key = Console.ReadKey(true);

                switch (_key.Key)
                {
                    case ConsoleKey.T:
                        {
                            #region
                            // Place code here
                            Console.WriteLine("\nType ID of tourist:\n");
                            _dr["TouristID"] = Console.ReadLine();
                            break;
                            #endregion
                        }

                    case ConsoleKey.C:
                        {
                            #region
                            // Place code here
                            Console.WriteLine("\nType Country:\n");
                            _dr["Country"] = Console.ReadLine();
                            break;
                            #endregion
                        }

                    case ConsoleKey.S:
                        {
                            #region
                            Console.WriteLine("\nType Start tour date:\n");
                            _dr["StartOfTour"] = Console.ReadLine();
                            break;
                            #endregion
                        }
                    case ConsoleKey.N:
                        {
                            #region
                            Console.WriteLine("{0}  {1}  {2}  {3}  {4}", TargetID, _dr.ItemArray[1],
                             _dr.ItemArray[2], _dr.ItemArray[3], _dr.ItemArray[4]);
                            break;
                            #endregion
                        }
                    case ConsoleKey.E:
                        {
                            #region
                            Console.WriteLine("\nType End tour date:\n");
                            _dr["EndOfTour"] = Console.ReadLine();
                            break;
                            #endregion
                        }

                    case ConsoleKey.Enter:
                        {
                            #region
                            Console.WriteLine("\n\nCAUTION: AFTER THIS OPERATION CHANGES WOULD NOT BE ALLOWED TO DECLINE!" +
                                "\nType: Y to ACCEPT/ N to DECLINE changes ");
                            _key = Console.ReadKey(true);
                            switch (_key.Key)
                            {
                                case ConsoleKey.Y:
                                    {
                                        _updAdapter.UpdateCommand = new SqlCommand("UPDATE " + TargetTable + " SET TouristID=@TouristID, "
                                            + "Country=@Country, StartOfTour=@StartOfTour, EndOfTour=@EndOfTour WHERE ToursID= '" + TargetID + "'"
                                            , DbConnect);

                                        SqlParameter UpdParam = null;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@TouristID", SqlDbType.Int));
                                        UpdParam.SourceColumn = "TouristID";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@Country", SqlDbType.NChar, 20));
                                        UpdParam.SourceColumn = "Country";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@StartOfTour", SqlDbType.Date));
                                        UpdParam.SourceColumn = "StartOfTour";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@EndOfTour", SqlDbType.Date));
                                        UpdParam.SourceColumn = "EndOfTour";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        UpdParam = _updAdapter.UpdateCommand.Parameters.Add(new SqlParameter
                                            ("@ToursID", SqlDbType.Int));
                                        UpdParam.SourceColumn = "ToursID";
                                        UpdParam.SourceVersion = DataRowVersion.Current;

                                        _updAdapter.Update(_dataSet3, "Tours");
                                        _updAdapter.Dispose();
                                        Console.WriteLine("\nAll changes are saved.");
                                        break;
                                    }
                                case ConsoleKey.N:
                                    {
                                        Console.WriteLine("\nAll changes are cleared, start again");
                                        _mark = false;
                                        break;
                                    }
                                default:
                                    {
                                        break;
                                    }
                            }

                            break;
                            #endregion
                        }

                    case ConsoleKey.B:
                        {
                            #region
                            _mark = false;
                            break;
                            #endregion
                        }
                }

            }
            Console.WriteLine();
            #endregion
        }
    }

    /// <summary>
    /// Sort input colums for Low to High or High to Low
    /// </summary>
    /// <param name="InfoOBject"></param>
    /// <param name="_output"></param>

    public void Sort(Info InfoObject, string _output)
    {
        DataSet _dataSet2 = new DataSet();
        SqlDataAdapter _dataAdapter = new SqlDataAdapter();

        string _sortColumn = "";
        string _sortProperty = "";
        string _curTable = InfoObject.WorkTable;
        string _query = "SELECT " + _output + " FROM " + _curTable;

        _dataAdapter.SelectCommand = new SqlCommand(_query, DbConnect);
        _dataAdapter.Fill(_dataSet2, _curTable);

        DataTable _dt = new DataTable();
        _dt = _dataSet2.Tables[0];

        Console.WriteLine("\nYours output has such columns\n");

        foreach (DataColumn column in _dt.Columns)
        {
            Console.Write(column.ColumnName.ToString() + "\t");
        }

        Console.WriteLine();
        Console.WriteLine("\nChoose column wich you wanna sort" +
           "\n" + InfoObject._collumnNames[0] + " - press  (key 1)" +
           "\n" + InfoObject._collumnNames[0] + " - press  (key 2)" +
           "\n" + InfoObject._collumnNames[0] + " - press (key 3)" +
           "\n" + InfoObject._collumnNames[0] + " - press   (key 4)" +
           "\n" + InfoObject._collumnNames[0] + " - press  (key 5)");

        ConsoleKeyInfo _key = new ConsoleKeyInfo();
    M: _key = Console.ReadKey(true);

        switch (_key.Key)
        {
            case ConsoleKey.D1:
                {
                    _sortColumn = InfoObject._collumnNames[0].ToString();
                    break;
                }
            case ConsoleKey.D2:
                {
                    _sortColumn = InfoObject._collumnNames[1].ToString();
                    break;
                }
            case ConsoleKey.D3:
                {
                    _sortColumn = InfoObject._collumnNames[2].ToString();
                    break;
                }
            case ConsoleKey.D4:
                {
                    _sortColumn = InfoObject._collumnNames[3].ToString();
                    break;
                }
            case ConsoleKey.D5:
                {
                    _sortColumn = InfoObject._collumnNames[4].ToString();
                    break;
                }
            default:
                {
                    Console.WriteLine("Wrong comand");
                    goto M;
                }
        }

        Console.WriteLine("\nIf you you wanna sort column Low to High (Key 1)" +
        "\nIf you you wanna sort column High to Low (Key 2)\n");
        _key = Console.ReadKey(true);

        switch (_key.Key)
        {
            case ConsoleKey.D1:
                {
                    _sortProperty = "ASC";
                    break;
                }
            case ConsoleKey.D2:
                {
                    _sortProperty = "DESC";
                    break;
                }
            default:
                {
                    Console.WriteLine("Wrong command");
                    break;
                }
        }

        _query = "SELECT " + _output + " FROM " + _curTable + " ORDER BY " + _sortColumn + " " + _sortProperty;
        SqlCommand command = new SqlCommand(_query, DbConnect);
        SqlDataReader _dataReader = command.ExecuteReader();

        while (_dataReader.Read())
        {
            for (int i = 0; i < _dataReader.FieldCount; i++)
            {
                Console.Write(" " + _dataReader[i]);
            }
            Console.WriteLine();
        }

        _dataReader.Close();
        Console.WriteLine("\nPress B to previous menu");
        _key = Console.ReadKey(true);

    }

    /// <summary>
    /// Forms outpur string for selections columns for output
    /// </summary>
    /// <param name="InfoObject"></param>

    public void FormOutput(Info InfoObject)
    {
        DataSet _dataSet2 = new DataSet();
        string _choise = "";
        string _selectedFields = " ";
        string _curTable = InfoObject.WorkTable;
        string _query = "SELECT * FROM " + _curTable;

        SqlDataAdapter _dataAdapter = new SqlDataAdapter();

        _dataAdapter.SelectCommand = new SqlCommand(_query, DbConnect);
        _dataAdapter.Fill(_dataSet2, _curTable);

        DataTable _dt = new DataTable();
        _dt = _dataSet2.Tables[0];

        Console.WriteLine("\nYours output has such columns\n");

        foreach (DataColumn column in _dt.Columns)
        {
            InfoObject._collumnNames.Add(column.ColumnName);
        }

        SqlCommand command = new SqlCommand(_query, DbConnect);
        SqlDataReader _dataReader = command.ExecuteReader();

        Console.WriteLine("Table has the following columns\n ");

        while (_dataReader.Read())
        {
            for (int i = 0; i < _dataReader.FieldCount; i++)
            {
                Console.Write(" " + _dataReader[i]);
            }
            Console.WriteLine();
        }

        _dataReader.Close();

        Console.WriteLine("\n\nChoose which of them you wanna to see in output" +
            "\nPress (Button 1) - to add {0} column to your output" +
            "\nPress (Button 2) - to add {1} column to your output" +
            "\nPress (Button 3) - to add {2} column to your output" +
            "\nPress (Button 4) - to add {3} column to your output" +
            "\nPress (Button 5) - to add {4} column to your output" +
            "\nPress (Button Enter) - to finish selection\n", InfoObject._collumnNames[0].ToString(),
            InfoObject._collumnNames[1].ToString(), InfoObject._collumnNames[2].ToString(),
            InfoObject._collumnNames[3].ToString(), InfoObject._collumnNames[4].ToString());

        ConsoleKeyInfo _key = new ConsoleKeyInfo();

        for (; ; )
        {

        M: _key = Console.ReadKey(true);

            if (_key.Key == ConsoleKey.Enter)
            {
                break;
            }

            switch (_key.Key)
            {
                case ConsoleKey.D1:
                    {
                        _choise += "\t1";
                        if (_selectedFields != " ")
                        {
                            _selectedFields += ",";
                        }
                        _selectedFields += InfoObject._collumnNames[0].ToString();
                        break;
                    }
                case ConsoleKey.D2:
                    {
                        _choise += "\t2";
                        if (_selectedFields != " ")
                        {
                            _selectedFields += ",";
                        }
                        _selectedFields += InfoObject._collumnNames[1].ToString();
                        break;
                    }
                case ConsoleKey.D3:
                    {
                        _choise += "\t3";
                        if (_selectedFields != " ")
                        {
                            _selectedFields += ",";
                        }
                        _selectedFields += InfoObject._collumnNames[2].ToString();
                        break;
                    }
                case ConsoleKey.D4:
                    {
                        _choise += "\t4";
                        if (_selectedFields != " ")
                        {
                            _selectedFields += ",";
                        }
                        _selectedFields += InfoObject._collumnNames[3].ToString();
                        break;
                    }
                case ConsoleKey.D5:
                    {
                        _choise += "\t5";
                        if (_selectedFields != " ")
                        {
                            _selectedFields += ",";
                        }
                        _selectedFields += InfoObject._collumnNames[4].ToString();
                        break;
                    }

                default:
                    {
                        goto M;
                    }

            }
            Console.WriteLine("Yours output columns is/are {0}", _choise);
        }
        Output = _selectedFields;
    }

    /// <summary>
    /// Groups input columns depents on selecnt mode
    /// </summary>
    /// <param name="InfoOBject"></param>
    /// <param name="_output"></param>

    public void Group()
    {
        int _touristID = 0;
        string _query = "";
        Console.WriteLine("\nTo see count of Tours in to all countries press                      (Button 1)" +
            "\nTo see in which countries and how many times visited tourist press   (Button 2)" +
            "\nTo see the tourists who visited the chosen country                   (Button 3)\n");

        ConsoleKeyInfo _key = new ConsoleKeyInfo();
    M: _key = Console.ReadKey(true);

    switch (_key.Key)
    {
        case ConsoleKey.D1:
            {
                _query = "SELECT Country, Count(Country) As Count from Tours GROUP BY Country";
                SqlCommand command = new SqlCommand(_query, DbConnect);
                SqlDataReader _dataReader = command.ExecuteReader();
                Console.WriteLine();

                while (_dataReader.Read())
                {
                    for (int i = 0; i < _dataReader.FieldCount; i++)
                    {
                        Console.Write(" " + _dataReader[i]);
                    }
                    Console.WriteLine();
                }
                _dataReader.Close();
                break;
            }
        case ConsoleKey.D2:
            {
                TouristsInfo _tI = new TouristsInfo();
                ShowTable(_tI);

                Console.WriteLine("\nType tourist ID to choose tourist");
                _touristID = (int)(Convert.ToInt32(Console.ReadLine()));

                _query = "SELECT Tourists.FirstName,Tourists.LastName from Tourists" +
                " Where Tourists.TouristID='" + _touristID + "'";
                string _query2 = "select Country, count(Country) as Quantity from " +
                    "Tours where TouristID='" + _touristID + "'" + " GROUP BY Country";
                SqlCommand command = new SqlCommand(_query, DbConnect);
                SqlCommand command2 = new SqlCommand(_query2, DbConnect);
                SqlDataReader _dataReader = command.ExecuteReader();
                Console.WriteLine();

                while (_dataReader.Read())
                {
                    for (int i = 0; i < _dataReader.FieldCount; i++)
                    {
                        Console.Write(" " + _dataReader[i]);
                    }
                    Console.WriteLine();
                }
                _dataReader.Close();

                _dataReader = command2.ExecuteReader();

                while (_dataReader.Read())
                {
                    for (int i = 0; i < _dataReader.FieldCount; i++)
                    {
                        Console.Write(" " + _dataReader[i]);
                    }
                    Console.WriteLine();
                }

                _dataReader.Close();
                Console.WriteLine();

                break;
            }
        case ConsoleKey.D3:
            {

                string _selectedCountry = "";
                string[] _countries = new string[10];
                int n = 0;

                _query = "SELECT Country from Tours GROUP BY Country";
                SqlCommand command = new SqlCommand(_query, DbConnect);
                SqlDataReader _dataReader = command.ExecuteReader();

                Console.WriteLine("Were made ​​tours to such countries, select a country :\n" +
                    "Enter the numrer of country in the list to see it's info\n");

                while (_dataReader.Read())
                {
                    n++;
                    for (int i = 0; i < _dataReader.FieldCount; i++)
                    {
                        _countries[i+n] = _dataReader[i].ToString();
                        Console.Write("{0} " + _dataReader[i] + "\n", n);
                    }
                }
                Console.WriteLine();
                _dataReader.Close();

            H: _key = Console.ReadKey(true);
                switch (_key.Key)
                {
                    case ConsoleKey.D1:
                        {
                            _selectedCountry = _countries[1];
                            break;
                        }
                    case ConsoleKey.D2:
                        {
                            _selectedCountry = _countries[2];
                            break;
                        }
                    case ConsoleKey.D3:
                        {
                            _selectedCountry = _countries[3];
                            break;
                        }
                    case ConsoleKey.D4:
                        {
                            _selectedCountry = _countries[4];
                            break;
                        }
                    case ConsoleKey.D5:
                        {
                            _selectedCountry = _countries[5];
                            break;
                        }
                    case ConsoleKey.D6:
                        {
                            _selectedCountry = _countries[6];
                            break;
                        }
                    case ConsoleKey.D7:
                        {
                            _selectedCountry = _countries[7];
                            break;
                        }
                    default:
                        {
                            Console.WriteLine("Wrong comand");
                            goto H;
                        }
                }

                _query = "select * from Tourists where TouristID IN" +
                    "(SELECT TouristID from Tours where Country" +
                    "= '" + _selectedCountry + "' group by TouristID)";
                SqlCommand command2 = new SqlCommand(_query, DbConnect);
                _dataReader = command2.ExecuteReader();

                Console.WriteLine("On tour to *{0}* traveled such tourists:\n", _selectedCountry.ToString());

                while (_dataReader.Read())
                {
                    for (int i = 0; i < _dataReader.FieldCount; i++)
                    {
                        Console.Write(" " + _dataReader[i]);
                    }
                    Console.WriteLine();
                }
                _dataReader.Close();
                break;
            }
        default:
            {
                Console.WriteLine("Wrong command");
                goto M;
            }
    }
        Console.WriteLine("\nPress B to previous menu");
        Console.ReadKey();
    }
}
}
